﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Win32;
using SweetSQL.Data;
using SweetSQL.Samples.Crud.Business;

namespace SweetSQL.Samples.Crud
{
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    public partial class Window1 : Window
    {
        private ConnectionParameters _params = new ConnectionParameters
            {
                ConnectionType = ConnectionType.SQLite,
                SyntaxType = SyntaxType.SQLite,
                DataSource = "Test.db",
                NoTransaction = true, //All our requests are only 1 db command
            },
        _excelParams = new ConnectionParameters
            {
                ConnectionType = ConnectionType.OleDb,
                SyntaxType = SyntaxType.Open,
                Provider = "Microsoft.Jet.OLEDB.4.0",
            };
        private SaveFileDialog _saveDialog = new SaveFileDialog();

        public Window1()
        {
            InitializeComponent();

            _saveDialog.Filter = "Excel File|*.xls";
            _saveDialog.OverwritePrompt = true;
            _saveDialog.Title = "Export to Excel";

            _params.CustomProperties["Version"] = "3";
            _excelParams.CustomProperties["Extended Properties"] = "\"Excel 7.0;HDR=YES;\"";
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            Refresh();
        }

        private void New_Click(object sender, RoutedEventArgs e)
        {
            InputWindow window = new InputWindow();
            window.Owner = this;
            if (window.ShowDialog().GetValueOrDefault())
            {
                using (Database database = new Database(_params))
                {
                    database.Insert(new Employee
                        {
                            ID = Guid.NewGuid(),
                            Name = window.NameText.Text,
                            Number = window.NumberText.Text.ToInt(),
                        });
                    database.Commit();
                }
                Refresh();
            }
        }

        private void Edit_Click(object sender, RoutedEventArgs e)
        {
            Employee employee = View.SelectedItem as Employee;
            if (employee != null)
            {
                InputWindow window = new InputWindow();
                window.Owner = this;
                window.NameText.Text = employee.Name;
                window.NumberText.Text = employee.Number.ToString();
                if (window.ShowDialog().GetValueOrDefault())
                {
                    using (Database database = new Database(_params))
                    {
                        employee.Name = window.NameText.Text;
                        employee.Number = window.NumberText.Text.ToInt();
                        database.Update(employee);
                        database.Commit();
                    }
                    Refresh();
                }
            }
        }

        private void Delete_Click(object sender, RoutedEventArgs e)
        {
            Employee employee = View.SelectedItem as Employee;
            if (employee != null)
            {
                using (Database database = new Database(_params))
                {
                    database.Delete(employee);
                    database.Commit();
                }
                Refresh();
            }
        }

        private void DeleteAll_Click(object sender, RoutedEventArgs e)
        {
            using (Database database = new Database(_params))
            {
                database.Delete<Employee>();
            }
            Refresh();
        }

        private void Generate_Click(object sender, RoutedEventArgs e)
        {
            using (Database database = new Database(_params))
            {
                database.BulkInsert(Generate());
                database.Commit();
            }
            Refresh();
        }

        private void Export_Click(object sender, RoutedEventArgs e)
        {
            if (_saveDialog.ShowDialog(this).GetValueOrDefault())
            {
                if (File.Exists(_saveDialog.FileName))
                {
                    File.Delete(_saveDialog.FileName);
                }
                _excelParams.DataSource = _saveDialog.FileName;
                using (Database database = new Database(_excelParams))
                {
                    database.CreateTable<ExcelEmployee>();
                    database.BulkInsert((View.ItemsSource as IEnumerable<Employee>)
                        .Select(emp => new ExcelEmployee { ID = emp.ID, Name = emp.Name, Number = emp.Number }));
                    database.Commit();
                }
            }
        }

        private void Refresh()
        {
            using (Database database = new Database(_params))
            {
                View.ItemsSource = database.Select<Employee>().ToArray();
            }
        }

        private IEnumerable<Employee> Generate()
        {
            for (int i = 0; i < 100; i++)
            {
                yield return new Employee
                    {
                        ID = Guid.NewGuid(),
                        Name = "TEST " + i,
                        Number = i,
                    };
            }
        }
    }
}
